package student_system.Dao;

import student_system.UnderAccessDBclass.Druid_utils;
import student_system.bean.course;
import student_system.bean.student;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class courseDao {
    public ArrayList<course> courseselect() {
        System.out.println("使用 druid 方式完成");
        //1. 得到连接
        Connection connection = null;
        String sql = "select * from course ";
        PreparedStatement preparedStatement = null;
        ResultSet set = null;
        ArrayList<course> list=new ArrayList<course>();

        try {
            connection = Druid_utils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            //给占位符赋值
            //preparedStatement.setInt(1, cno);
            //得到结果集
            set = preparedStatement.executeQuery();
            while (set.next()) {
                String cno = set.getString("cno");
                String cname = set.getString("cname");
                int credit = set.getInt("credit");
                String ctime = set.getString("ctime");
                int quota = set.getInt("quota");
                list.add(new course(cno,cname,credit,ctime,quota));
                //System.out.println(id + "\t" + name + "\t" + sex + "\t" + borndate + "\t" + phone);
            }
            System.out.println(list);
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("yichang");
        } finally {

            Druid_utils.close(set, preparedStatement, connection);

        }
        //将ResultSet封装到ArrayList
        return  list;
    }

    public   void delete(String cno){
        Connection connection=null;
        PreparedStatement preparedStatement=null;

        try {
            connection = Druid_utils.getConnection();
            String sql="DELETE FROM course WHERE cno=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,cno);
            preparedStatement.executeUpdate();
            System.out.println("success");
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("delete_error");
            Druid_utils.close(null,preparedStatement,connection);
        }

    }

    public  void addcourse(String cno,String cname,int credit,String ctime,int quota){
        try {
            Connection connection = Druid_utils.getConnection();
            String sql="insert into course values (?,?,?,?,?)" ;
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,cno);
            preparedStatement.setString(2,cname);
            preparedStatement.setInt(3,credit);
            preparedStatement.setString(4,ctime);
            preparedStatement.setInt(5,quota);
            preparedStatement.executeUpdate();
            System.out.println("success");

        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("error");
        }
    }

    public  void alterBycno(String cno,String cname,int credit,String ctime,int quota) {
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        try {
            connection = Druid_utils.getConnection();
            String sql = "update course set cname=?,credit=?,ctime=?,quota=? where cno=?";
            preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setString(1, cname);
            preparedStatement.setInt(2, credit);
            preparedStatement.setString(3, ctime);
            preparedStatement.setInt(4, quota);
            preparedStatement.setString(5, cno);
            preparedStatement.executeUpdate();
            System.out.println("success");

        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("修改error");

        }finally {
            Druid_utils.close(null,preparedStatement,connection);
        }
    }

    /**
     * 查询course表中信息
     * 使用为course_alter.java
     * @param cno
     * @return
     */

    public  course selectbycno( String cno) {
        //1. 得到连接
        Connection connection = null;
        String sql = "select * from course where cno = ?";
        PreparedStatement preparedStatement = null;
        ResultSet set = null;
        course course = null;
        try {
            connection = Druid_utils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            //给占位符赋值
            preparedStatement.setString(1, cno);
            //得到结果集
            set = preparedStatement.executeQuery();
            while (set.next()) {
                course = new course(
                        set.getString(1),
                        set.getString(2),
                        set.getInt(3),
                        set.getString(4),
                        set.getInt(5));
            }
            //System.out.println(list);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Druid_utils.close(set, preparedStatement, connection);
        }
        return course;
    }

}
